"""
数据库基础操作类
提供通用的数据库操作方法：批量查询、插入、删除
"""

import aiomysql
from typing import List, Dict, Any, Optional, Union, Literal
from db import DatabaseConnection, db_connection


class BaseOperations:
    """数据库基础操作类"""

    def __init__(self, table_name: str):
        self.db_connection: DatabaseConnection = db_connection
        self.table_name = table_name

    async def batch_query(
        self,
        where_clause: str = "",
        params: Optional[tuple] = None,
        limit: Optional[int] = None,
        offset: int = 0,
        order_by: str = "",
    ) -> List[Dict[str, Any]]:
        """
        批量查询记录

        Args:
            where_clause (str): WHERE子句（不包含WHERE关键字）
            params (Optional[tuple]): 参数
            limit (Optional[int]): 限制数量
            offset (int): 偏移量
            order_by (str): 排序子句（不包含ORDER BY关键字）

        Returns:
            List[Dict[str, Any]]: 查询结果列表
        """
        sql = f"SELECT * FROM {self.table_name}"

        if where_clause:
            sql += f" WHERE {where_clause}"

        if order_by:
            sql += f" ORDER BY {order_by}"

        if limit is not None:
            sql += f" LIMIT {limit}"
            if offset > 0:
                sql += f" OFFSET {offset}"

        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute(sql, params)
                    results = await cursor.fetchall()
                    return results if results else []
        except Exception as e:
            print(f"批量查询失败: {e}")
            return []

    async def batch_insert(
        self,
        data_list: List[Dict[str, Any]],
        on_duplicate: Literal["skip", "update"] = "skip",
    ) -> int:
        """
        批量插入记录

        Args:
            data_list (List[Dict[str, Any]]): 要插入的数据列表
            on_duplicate (str): 重复处理策略 - "skip"(跳过), "update"(更新) 默认为"skip"

        Returns:
            int: 成功插入的记录数量
        """
        if not data_list:
            return 0
        # 从第一条数据获取字段列表
        fields = list(data_list[0].keys())
        placeholders = ", ".join(["%s"] * len(fields))

        # 根据重复处理策略构建SQL
        if on_duplicate == "update":
            # 获取表的主键信息，避免更新主键字段
            # 对于大多数情况，我们不应该更新主键字段
            # 这里可以根据具体需求排除某些字段
            update_fields = [f"{field} = VALUES({field})" for field in fields]
            sql = (
                f"INSERT INTO {self.table_name} ({', '.join(fields)}) VALUES ({placeholders}) "
                f"ON DUPLICATE KEY UPDATE {', '.join(update_fields)}"
            )
        else:
            sql = f"INSERT IGNORE INTO {self.table_name} ({', '.join(fields)}) VALUES ({placeholders})"

        # 构建参数列表
        params_list = []
        for data in data_list:
            params_list.append(tuple(data[field] for field in fields))

        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.executemany(sql, params_list)
                    return cursor.rowcount
        except Exception as e:
            print(f"批量插入失败: {e}")
            return 0

    # 注意：在大多数情况下，可以使用 batch_insert(on_duplicate="update") 来替代此方法
    # batch_insert 使用 INSERT ... ON DUPLICATE KEY UPDATE，性能更好
    # 但 batch_update 提供了更精确的控制，仅更新已存在的记录
    async def batch_update(
        self, data_list: List[Dict[str, Any]], primary_key: str
    ) -> int:
        """
        批量更新记录

        Args:
            data_list (List[Dict[str, Any]]): 要更新的数据列表，每个字典必须包含主键
            primary_key (str): 主键字段名，默认为"id"

        Returns:
            int: 成功更新的记录数量
        """
        if not data_list:
            return 0

        pool = await self.db_connection.get_pool()
        success_count = 0

        try:
            async with pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    for data in data_list:
                        if primary_key not in data:
                            print(
                                f"警告：更新数据中缺少主键 {primary_key}，跳过该条记录"
                            )
                            continue

                        # 获取主键值
                        pk_value = data[primary_key]

                        # 构建更新字段（排除主键）
                        update_fields = {
                            k: v for k, v in data.items() if k != primary_key
                        }

                        if not update_fields:
                            continue

                        # 构建SET子句
                        set_clauses = []
                        params = []

                        for field, value in update_fields.items():
                            set_clauses.append(f"{field} = %s")
                            params.append(value)

                        # 添加WHERE条件的参数
                        params.append(pk_value)

                        # 构建UPDATE SQL
                        sql = f"UPDATE {self.table_name} SET {', '.join(set_clauses)} WHERE {primary_key} = %s"

                        await cursor.execute(sql, tuple(params))
                        if cursor.rowcount > 0:
                            success_count += 1

                    await conn.commit()
                    return success_count

        except Exception as e:
            print(f"批量更新失败: {e}")
            return 0

    async def batch_delete(
        self,
        where_conditions: List[Dict[str, Any]] = None,
        where_clause: str = "",
        params: Optional[tuple] = None,
    ) -> int:
        """
        批量删除记录

        Args:
            where_conditions (List[Dict[str, Any]]): 删除条件列表，每个字典表示一个删除条件
            where_clause (str): 通用WHERE子句（不包含WHERE关键字）
            params (Optional[tuple]): 通用WHERE子句的参数

        Returns:
            int: 删除的记录数量
        """
        pool = await self.db_connection.get_pool()
        total_deleted = 0

        try:
            async with pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    # 如果提供了具体的删除条件列表
                    if where_conditions:
                        for condition in where_conditions:
                            if not condition:
                                continue

                            # 构建WHERE子句
                            where_parts = []
                            condition_params = []
                            for field, value in condition.items():
                                where_parts.append(f"{field} = %s")
                                condition_params.append(value)

                            if where_parts:
                                sql = f"DELETE FROM {self.table_name} WHERE {' AND '.join(where_parts)}"
                                await cursor.execute(sql, tuple(condition_params))
                                total_deleted += cursor.rowcount

                    # 如果提供了通用WHERE子句
                    elif where_clause:
                        sql = f"DELETE FROM {self.table_name} WHERE {where_clause}"
                        await cursor.execute(sql, params)
                        total_deleted = cursor.rowcount

                    return total_deleted

        except Exception as e:
            print(f"批量删除失败: {e}")
            return 0
